﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace GenSql
{
    class Program
    {
        [STAThreadAttribute]
        static void Main(string[] args)
        {
            SqlConnection cn = new SqlConnection("Data Source=192.168.2.45;Database=MikroDB_V14_YASIN;user id=sa");
            cn.Open();

            string tablo = "STOK_SERINO_TANIMLARI";

            SqlCommand cmd = new SqlCommand(string.Format("select TOP 1 * from {0}", tablo), cn);
            SqlDataReader rd = cmd.ExecuteReader();

            StringBuilder sb = new StringBuilder();

            string keyfield = string.Empty;
            for (int i = 0; i < rd.FieldCount; i++)
            {
                if (rd.GetName(i).IndexOf("_serino") > -1)
                {
                    keyfield = rd.GetName(i);
                    break;
                }
            }
            rd.Read();

            sb.Append("StringBuilder sb = new StringBuilder();\r\n");
            sb.Append("sb.Append(\"" + string.Format("if exists(select {0} from {1} where {2}= '{3}')", rd.GetName(0), tablo, keyfield, rd[keyfield]) + "\");\r\n");

            string updatesql = string.Empty;
            string fields = string.Empty;
            string values = string.Empty;

            for (int i = 1; i < rd.FieldCount; i++)
            {
                if (rd.GetName(i).IndexOf("_kod") == -1 && i > 2)
                {
                    switch (rd.GetDataTypeName(i))
                    {
                        case "datetime":
                            updatesql += string.Format("{0}='{1}',", rd.GetName(i), string.Format("{0}-{1}-{2} {3}:{4}:{5}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second));
                            break;
                        case "nvarchar":
                        case "char":
                        case "varchar":
                            updatesql += string.Format("{0}='{1}',", rd.GetName(i), rd[i]);
                            break;
                        case "bit":
                            if (rd.GetBoolean(i) == false)
                                updatesql += string.Format("{0}=0,", rd.GetName(i));
                            else
                                updatesql += string.Format("{0}=1,", rd.GetName(i));
                            break;
                        default:
                            updatesql += string.Format("{0}={1},", rd.GetName(i), rd[i]);
                            break;
                    }
                }


                fields += rd.GetName(i) + ",";
                switch (rd.GetDataTypeName(i))
                {
                    case "datetime":
                        values += "'" + string.Format("{0}-{1}-{2} {3}:{4}:{5}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second) + "',";
                        break;
                    case "nvarchar":
                    case "varchar":
                        values += "'" + rd[i] + "',";
                        break;
                    case "bit":
                        if (rd.GetBoolean(i) == false)
                            values += "0,";
                        else
                            values += "1,";
                        break;
                    default:
                        values += rd[i] + ",";
                        break;
                }

            }

            updatesql = updatesql.Remove(updatesql.Length - 1, 1);
            fields = fields.Remove(fields.Length - 1, 1);
            values = values.Remove(values.Length - 1, 1);

            sb.Append("sb.Append(\"" + string.Format("update {0} set {1} where {2}='{3}' else ", tablo, updatesql, keyfield, rd[keyfield]) + "\");\r\n");
            sb.Append("sb.Append(\"" + string.Format("insert into {0} ({1}) values ({2}) ", tablo, fields, values) + "\");\r\n");
            sb.Append("sb.Append(\"" + string.Format(";update {0} set {1}={2} where {1}=-1", tablo, rd.GetName(2), rd.GetName(0)) + "\");");




            Clipboard.SetText(sb.ToString());

        }
    }
}
